﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Printing;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraPrinting;
//using Lamex.mDashboard.Common;
using Microsoft.Office.Interop.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using DataTable = System.Data.DataTable;

namespace Lamex.Common
{
    public class ExecelHelper
    {
        public static string Version { get; private set; }

        public static System.Data.DataTable ExcelToDataTable(string ExcelPath)
        {
            return ExcelToDataTable(ExcelPath, null);
        }
        /// <summary>
        /// 开始转化成表格
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public static System.Data.DataTable ExcelToDataTable(string ExcelPath, string SheetName)
        {
            string conStr = GetConStr(ExcelPath);
            if (string.IsNullOrEmpty(conStr))
                return null;
            OleDbConnection connection = new OleDbConnection(conStr);
            connection.Open();
            if (string.IsNullOrEmpty(SheetName))
                SheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            else if (!SheetName.Contains("$"))
                SheetName = SheetName + "$";
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + SheetName + "]", conStr);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "[" + SheetName + "$]");
            connection.Close();
            return dataSet.Tables[0];
        }
        /// <summary>
        /// 读取excel类型
        /// </summary>
        /// <param name="ExcelPath"></param>
        /// <returns></returns>
        private static string GetConStr(string ExcelPath)
        {
            string path = ExcelPath;
            if (!File.Exists(path))
                return null;
            string str2 = Path.GetExtension(path).ToLower();
            if ((str2 != ".xls") && (str2 != ".xlsx"))
                return null;
            string str3 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + "; Extended Properties=Excel 8.0";
            if (str2 == ".xlsx")
                str3 = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0";
            return str3;
        }

        public static System.Data.DataTable ExcelToDataTable(string FileName, int Index)
        {
            //OpenFileDialog openFile = new OpenFileDialog();
            //openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            //openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            //openFile.Multiselect = false;
            //if (openFile.ShowDialog() == DialogResult.Cancel) return null;
            var excelFilePath = FileName;

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Sheets sheets;
            object oMissiong = System.Reflection.Missing.Value;
            Workbook workbook = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            bool hasTitle = true;
            try
            {
                if (app == null) return null;

                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                //workbook.Protect("QQQQ");
                sheets = workbook.Worksheets;

                //将数据读入到DataTable中
                Worksheet worksheet = (Worksheet)sheets.get_Item(Index);//读取第几张表 
                if (worksheet == null) return null;

                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                //生成列头
                for (int i = 0; i < iColCount; i++)
                {
                    var name = "column" + i;
                    if (hasTitle)
                    {
                        var txt = ((Range)worksheet.Cells[1, i + 1]).Text.ToString();
                        if (!string.IsNullOrEmpty(txt)) name = txt;
                    }
                    while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                    dt.Columns.Add(new DataColumn(name, typeof(string)));
                }
                //生成行数据
                Range range;
                int rowIdx = hasTitle ? 2 : 1;
                for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
                {
                    DataRow dr = dt.NewRow();
                    int m = 0;
                    for (int iCol = 1; iCol <= iColCount; iCol++)
                    {
                        //range = (Range)worksheet.Cells[iRow, 22];
                        //if (range.Value2 != null)
                        //{
                        //    m = 1;
                        //    break;

                        //}
                        range = (Range)worksheet.Cells[iRow, iCol];
                        dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
                    }
                    if (m != 1)
                    {
                        dt.Rows.Add(dr);
                    }
                }



                return dt;
            }
            catch { return null; }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Visible = false;
                app.DisplayAlerts = false;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                //GC.Collect();  
                foreach (Process p in Process.GetProcessesByName("Excel"))
                {
                    if (string.IsNullOrEmpty(p.MainWindowTitle))
                    {
                        p.Kill();
                    }
                }
            }
        }

        public static void ExportToExcel(GridView gridView, string 文件名)
        {
            System.Data.DataTable dt = (gridView.DataSource as System.Data.DataView).Table;
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Excel文件(*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;            //dlg.CreatePrompt = true;            
            dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            dlg.FileName = string.Format("{0}_{1}", DateTime.Now.ToString("yyyy-MM-dd HH时mm分"), 文件名);
            if (dlg.ShowDialog() != DialogResult.OK) return;
            string filePath = dlg.FileName;
            using (DevExpress.Utils.WaitDialogForm wdf = new DevExpress.Utils.WaitDialogForm("请稍等", "正在导出数据...", new Size(400, 80)))
            {
                int OLDOFFICEVESION = -4143;
                int NEWOFFICEVESION = 56;                //保存excel文件的格式                
                int FormatNum;                //excel版本号                string Version;                //启动应用                
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;                //创建文件                
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);                //创建sheet                
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];                //获取你使用的excel 的版本号                
                Version = xlApp.Version;                //使用Excel 97-2003                
                if (Convert.ToDouble(Version) < 12)
                {
                    FormatNum = OLDOFFICEVESION;
                }                //使用 excel 2007或更新                
                else
                {
                    FormatNum = NEWOFFICEVESION;
                }
                //添加输出excel表格的表头信息信息                
                //注意这里的excel对应的单元格第一个位置为[1,1]，而不是我们平时定义[0,0]                
                for (int i = 0; i < gridView.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = gridView.Columns[i].FieldName;
                }
                //添加输出excel表格的内容信息                
                for (int rowIndex = 1; rowIndex < dt.Rows.Count + 1; rowIndex++)
                {
                    DataRow rowArr = dt.Rows[rowIndex - 1];
                    for (int cellIndex = 0; cellIndex < gridView.Columns.Count; cellIndex++)
                    {
                        string aa = rowArr[cellIndex].ToString();
                        worksheet.Cells[rowIndex + 1, cellIndex + 1] = rowArr[gridView.Columns[cellIndex].FieldName];
                    }
                }
                //删除已存在的excel文件，否则会无法保存创建的excel文件                
                if (File.Exists(filePath))
                {
                    try
                    {
                        File.Delete(filePath);
                    }
                    catch (IOException e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                //保存，这里必须指定FormatNum文件的格式，否则无法打开创建的excel文件                
                workbook.SaveAs(filePath, FormatNum);
                //显示创建的excel文件，true打开Excel，false隐藏Excel                
                xlApp.Visible = false;                //关闭Excel进程                
                xlApp.Quit();
            }
        }

        public static System.Data.DataTable ImportToTable(string path)
        {
            //string fName="";
            OpenFileDialog open = new OpenFileDialog();
            open.InitialDirectory = "D:\\";
            open.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            //if (open.ShowDialog() == DialogResult.OK) { path = open.FileName; }
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意             
            OleDbConnection conn = new OleDbConnection(connstring);
            try
            {   //连接字符串 
                //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本
                conn.Open(); System.Data.DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字                
                string firstSheetName = "Sheet1$"; //得到第一个sheet的名字                 
                string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串 
                //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串       
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);
                return set.Tables[0];
            }
            catch (Exception ex)
            {
                return null;
                conn.Close();
            }
        }

        private string GetConStr2(string ExcelPath)
        {
            string path = ExcelPath;
            if (!File.Exists(path))
                return null;
            string str2 = Path.GetExtension(path).ToLower();
            if ((str2 != ".xls") && (str2 != ".xlsx"))
                return null;
            string str3 = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + path + "; Extended Properties=Excel 8.0";
            if (str2 == ".xlsx")
                str3 = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0";
            return str3;
        }

        public System.Data.DataTable ExcelToDataTabl2(string ExcelPath)
        {
            return ExcelToDataTable2(ExcelPath, null);
        }

        public System.Data.DataTable ExcelToDataTable2(string ExcelPath, string SheetName)
        {
            string conStr = GetConStr(ExcelPath);
            if (string.IsNullOrEmpty(conStr))
                return null;
            OleDbConnection connection = new OleDbConnection(conStr);
            connection.Open();
            if (string.IsNullOrEmpty(SheetName))
                SheetName = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            else if (!SheetName.Contains("$"))
                SheetName = SheetName + "$";
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + SheetName + "]", conStr);
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet, "[" + SheetName + "$]");
            connection.Close();
            return dataSet.Tables[0];
        }
        private static OleDbConnection conn = new OleDbConnection();
        public static int GetCoulumNum(string FileName, int Index, ref string ExcelName)
        {


            var excelFilePath = FileName;

            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Sheets sheets;
            object oMissiong = System.Reflection.Missing.Value;
            Workbook workbook = null;
            System.Data.DataTable dt = new System.Data.DataTable();
            bool hasTitle = true;
            try
            {
                if (app == null) return 0;

                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
                    oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                //workbook.Protect("QQQQ");
                sheets = workbook.Worksheets;

                //将数据读入到DataTable中
                Worksheet worksheet = (Worksheet)sheets.get_Item(Index);//读取第几张表 
                ExcelName = worksheet.Name;
                if (worksheet == null) return 0;

                int iRowCount = worksheet.UsedRange.Rows.Count;
                int iColCount = worksheet.UsedRange.Columns.Count;
                //生成列头
                for (int i = 0; i < iColCount; i++)
                {
                    var name = "column" + i;
                    if (hasTitle)
                    {
                        var txt = ((Range)worksheet.Cells[1, i + 1]).Text.ToString();
                        if (!string.IsNullOrEmpty(txt)) name = txt;
                    }
                    while (dt.Columns.Contains(name)) name = name + "_1";//重复行名称会报错。
                    dt.Columns.Add(new DataColumn(name, typeof(string)));
                }
                
                return dt.Columns.Count;
            }
            catch { return 0; }
            finally
            {
                workbook.Close(false, oMissiong, oMissiong);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                app.Visible = false;
                app.DisplayAlerts = false;
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
                //GC.Collect();  
                foreach (Process p in Process.GetProcessesByName("Excel"))
                {
                    if (string.IsNullOrEmpty(p.MainWindowTitle))
                    {
                        p.Kill();
                    }
                }
            }
        }
        public static int GetExcelColumnNum(int sheetNum, bool isFirstRowColumn, string fileName, ref string ExcelName,ref int RowNum, ref string strColName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable myTable = new DataTable();
            try
            {
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs);
                sheet = workbook.GetSheetAt(sheetNum);
                ExcelName = sheet.SheetName;
                RowNum = sheet.LastRowNum;
                //工作表不能为空
                if (sheet == null)
                {
                    string str = "";
                    for (int i = 0; i < workbook.NumberOfSheets; i++)
                    {
                        str += workbook.GetSheetAt(i).SheetName + ",";
                    }
                    str = workbook.NumberOfSheets + str;
                    throw new Exception("$sheet不能为空！参数：{sheetNum} 工作簿信息：{str}");
                }



                //Excel最大列数
                int MaxColumnNum = 0;
                int m = sheet.FirstRowNum;

                var row = sheet.GetRow(m);

                //if (row.LastCellNum > MaxColumnNum)
                //{
                //    MaxColumnNum = row.LastCellNum;
                //}
                MaxColumnNum = row.PhysicalNumberOfCells;

                strColName = "";
                foreach (ICell s in row.Cells)
                {
                    strColName += s.StringCellValue;
                }
                //for (int i = 0; i < sheet.LastRowNum; i++)
                //{
                //    //var row = sheet.GetRow(i);
                //    //if (row.LastCellNum > MaxColumnNum)
                //    //{
                //    //    MaxColumnNum = row.LastCellNum;
                //    //}
                //}
                return MaxColumnNum;
            }
            catch(Exception ex)
            {
                workbook.Close();

                throw ex;
            }
        }
        /// <summary>
        /// Excel 转换为 Datatable
        /// </summary>
        /// <param name="sheetNum">工作表索引</param>
        /// <param name="isFirstRowColumn">首行为列</param>
        /// <param name="fileName">Excel文件路径</param>
        /// <returns></returns>
        
        public static System.Data.DataTable ExcelConvertDataTable(int sheetNum, bool isFirstRowColumn, string fileName)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable myTable = new DataTable();
            string irows = "";
            string icols = "";
            try
            {
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                if (fileName.IndexOf(".xlsx") > 0)
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0)
                    workbook = new HSSFWorkbook(fs);
                sheet = workbook.GetSheetAt(sheetNum);

                //工作表不能为空
                if (sheet == null)
                {
                    string str = "";
                    for (int i = 0; i < workbook.NumberOfSheets; i++)
                    {
                        str += workbook.GetSheetAt(i).SheetName + ",";
                    }
                    str = workbook.NumberOfSheets + str;
                    throw new Exception("$sheet不能为空！参数：{sheetNum} 工作簿信息：{str}");
                }

                //Excel最大列数
                int MaxColumnNum = 0;
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    if (row.LastCellNum > MaxColumnNum)
                    {
                        MaxColumnNum = row.LastCellNum;
                    }
                }
                //Excel行数
                int MaxRowNum = sheet.LastRowNum;

                //table新增列
                for (int i = 0; i < MaxColumnNum; ++i)
                {
                    //首行为列
                    irows = "f" + i.ToString();
                    if (isFirstRowColumn)
                    {
                        icols ="f"+ i.ToString();
                        bool addEmptyCell = true;//是否添加空列
                        ICell cell = sheet.GetRow(0).GetCell(i);
                        if (cell != null)
                        {
                            //table列赋值
                            string cellValue = "";//列名
                            if (cell.CellType == CellType.Numeric)
                            {
                                cellValue = cell.NumericCellValue.ToString();
                            }
                            //else if (cell.CellType == CellType.String)
                            //{
                            //    cellValue = cell.StringCellValue;
                            //}
                            else
                            {
                                cellValue = cell.StringCellValue;
                            }
                            if (!string.IsNullOrWhiteSpace(cellValue))
                            {
                                //列数据为Excel的数据
                                addEmptyCell = false;
                                myTable.Columns.Add(new DataColumn(cellValue));
                            }
                        }
                        if (addEmptyCell)
                        {
                            myTable.Columns.Add(new DataColumn(""));//列数据为空
                        }
                    }
                    else
                    {
                        myTable.Columns.Add(new DataColumn(i + ""));
                    }
                }

                //起始行
                int startRow = 0;
                if (isFirstRowColumn)
                {
                    startRow = 1;
                }

                //DataTable赋值
                for (int i = startRow; i <= MaxRowNum; ++i)
                {
                    irows = i.ToString();
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue;
                   
                    DataRow NewRow = myTable.NewRow();
                    int iCountCheckNull = 0;
                    for (int j = row.FirstCellNum; j < myTable.Columns.Count; ++j)
                    {
                        
                        icols = j.ToString();
                        ICell cell = row.GetCell(j);
                        string value = "";
                        if (cell != null)
                        {
                            
                                //table行赋值                            
                                if (cell.CellType == CellType.Numeric)
                                {
                                    //error2 = cell.StringCellValue;
                                    //short format = cell.CellStyle.DataFormat;
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                    string date = "";
                                    if (cell.DateCellValue.ToString().Contains("1899/12/31"))
                                    {
                                        string[] arr = cell.DateCellValue.ToString().Split(' ');
                                        date = arr[1];
                                    }
                                    else
                                        date = cell.DateCellValue.ToString();

                                        value = date;
                                    }
                                    else { value = cell.NumericCellValue.ToString(); }

                                }
                                else
                                {
                                //row.GetCell(j).SetCellType(CellType.String);

                                //value = cell.StringCellValue.ToString();
                                if (cell.CellType == CellType.Formula)
                                {
                                    row.GetCell(j).SetCellType(CellType.String);
                                    value = row.GetCell(j).StringCellValue;
                                }
                                else
                                {
                                    value = cell.StringCellValue.ToString();
                                }
                                    


                            }


                            NewRow[j] = value;
                            if (string.IsNullOrEmpty(value))
                                iCountCheckNull++;

                        }
                        else
                        {
                            
                                iCountCheckNull++;
                        }
                        
                    }
                    if(iCountCheckNull!=myTable.Columns.Count)
                        myTable.Rows.Add(NewRow);
                }
                return myTable;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                workbook.Close();
                
                throw ex;
            }
        }


        public static bool DataTableToExcel(DataTable dt, string FilePath)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            int iRow = 0;
            int iCol = 0;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    // workbook = new XSSFWorkbook();//office 2007

                    // 2007版本  
                    if (FilePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook();
                    // 2003版本  
                    else if (FilePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook();

                    sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet0的表 
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数
                                                       //设置列头  
                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }
                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        iRow = i;
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            ////转换为数字格式
                            //iCol = j;
                            //if (dt.Columns[j].ColumnName == "体积" || dt.Columns[j].ColumnName == "重量")
                            //{
                            //    double values = dt.Rows[i][j].ToString().Length > 0 ?
                            //        Convert.ToDouble(dt.Rows[i][j].ToString()) : 0;
                            //    cell = row.CreateCell(j, CellType.Numeric);
                            //    cell.SetCellValue(values);
                            //}
                            //else
                            //{
                            cell = row.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            //}


                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {

                if (fs != null)
                {
                    fs.Close();
                }
                XtraMessageBox.Show("第"+iRow.ToString()+"行"+"第"+iCol.ToString()+"列"+ ex.Message);
                return false;

            }
        }

        /// <summary>  
        /// 将excel导入到datatable--MES  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public DataTable ExcelToDataTableMES(string filePath, bool isColumnName)
        {
            //Caculates caculate = new Caculates();
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行 
                                int cellCount = firstRow.LastCellNum;//列数  
                                //构建datatable的列 
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                    //固定新增四列
                                    //dataTable.Columns.Add("MES工时");
                                    //dataTable.Columns.Add("综合工时", typeof(double));
                                    //dataTable.Columns.Add("综合成本工时",typeof(double));
                                    //dataTable.Columns.Add("差异");
                                    //dataTable.Columns.Add("备注");
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);

                                    }
                                }
                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;
                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        //if (j < 25)//读取Excel本身字段值
                                        // {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                                case CellType.Formula://单元格有函数
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                        //  }
                                        //获取新增四列的值
                                        // else
                                        //{
                                        //    switch (j)
                                        //    {

                                        //        case 25://MES工时
                                        //            /*12为工序；7为部件号，14为SAP工时
                                        //             *部件号为9开头的取SAP工时
                                        //             */
                                        //            dataRow[j] = caculate.GetMES_Hours(dataRow[12].ToString(), dataRow[7].ToString(), Convert.ToDouble(dataRow[14]));
                                        //            break;

                                        //        case 26://综合工时
                                        //            /*12为工序；7为部件号，14为SAP工时
                                        //             *部件号为9开头的取SAP工时
                                        //             */
                                        //            dataRow[j] = caculate.Get_ZHHours(Convert.ToDouble(dataRow[25]), Convert.ToDouble(dataRow[14]));
                                        //            break;

                                        //        case 27: //综合成本工时
                                        //            /*21工作中心类别;26综合工时;22单机定员;23管理系数;9部件数量;1订单数量*/
                                        //            dataRow[j] = caculate.GetMES_WorkStationValue(dataRow[21].ToString(),
                                        //                Convert.ToDouble(dataRow[26]), Convert.ToDouble(dataRow[22]),
                                        //                Convert.ToDouble(dataRow[23]), Convert.ToDouble(dataRow[9]),
                                        //                Convert.ToDouble(dataRow[1]));
                                        //                break;

                                        //        case 28://差异对比
                                        //            dataRow[j] = caculate.Get_DifferenceValue(Convert.ToDouble(dataRow[25]), Convert.ToDouble(dataRow[14]));
                                        //            break;

                                        //        case 29://备注
                                        //            dataRow[j] = caculate.Mark(dataRow[12].ToString(), dataRow[7].ToString());
                                        //            break;
                                        //    }

                                        //}
                                    }
                                    dataTable.Rows.Add(dataRow);

                                }

                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {

                if (fs != null)
                {
                    fs.Close();
                }
                return null;

            }
        }
        /// <summary>  
        /// 将excel导入到datatable--PPC  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns> 
        public DataTable ExcelToDataTablePPC(DataTable dt, string filePath, bool isColumnName)
        {
            //Caculates caculate = new Caculates();
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行 
                                int cellCount = firstRow.LastCellNum + 1;//列数  
                                //构建datatable的列 
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                    //固定新增1列
                                    dataTable.Columns.Add("MES生产完成标准工时(m)", typeof(double));

                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);

                                    }
                                }
                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;
                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        if (j < 22)//读取Excel本身字段值
                                        {
                                            cell = row.GetCell(j);
                                            if (cell == null)
                                            {
                                                dataRow[j] = "";
                                            }
                                            else
                                            {
                                                switch (cell.CellType)
                                                {
                                                    case CellType.Blank:
                                                        dataRow[j] = "";
                                                        break;
                                                    case CellType.Numeric:
                                                        short format = cell.CellStyle.DataFormat;
                                                        if (format == 14 || format == 31 || format == 57 || format == 58)
                                                            dataRow[j] = cell.DateCellValue;
                                                        else
                                                            dataRow[j] = cell.NumericCellValue;
                                                        break;
                                                    case CellType.String:
                                                        dataRow[j] = cell.StringCellValue;
                                                        break;
                                                    case CellType.Formula://单元格有函数
                                                        dataRow[j] = cell.StringCellValue;
                                                        break;
                                                }
                                            }
                                        }
                                        //获取新增1列的值
                                        else
                                        {
                                            //4为订单号；13为InsideQty
                                            //dataRow[j] = Math.Round(Convert.ToDouble(caculate.Get_PPCResult(dt, dataRow[4].ToString())) * Convert.ToDouble(dataRow[13]) / 60, 2);
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);

                                }

                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {

                if (fs != null)
                {
                    fs.Close();
                }
                return null;

            }
        }


        public static bool ExportToExcelHeader(GridView gridView, string FilePath)
        {
            System.Data.DataTable dt = (gridView.DataSource as System.Data.DataView).Table;

            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            int iRow = 0;
            int iCol = 0;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    // workbook = new XSSFWorkbook();//office 2007

                    // 2007版本  
                    if (FilePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook();
                    // 2003版本  
                    else if (FilePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook();

                    sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet0的表 
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数//设置列头  

                    row = sheet.CreateRow(0);//excel第一行设为列头  
                    
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                        cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
                        cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                        cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                        cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                    }
                    //设置每行每列的单元格,  
                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        iRow = i;
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            //转换为数字格式
                            iCol = j;

                            cell = row.CreateCell(j);//excel第二行开始写入数据  
                            if(dt.Columns[j].DataType.ToString() != "System.Byte[]")
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            else
                                cell.SetCellValue("");
                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        fs.Close();
                        workbook.Close();
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {

                if (fs != null)
                {
                    fs.Close();
                    workbook.Close();
                }
                XtraMessageBox.Show("第" + iRow.ToString() + "行" + "第" + iCol.ToString() + "列" + ex.Message);
                return false;

            }
        }


        public static bool ExportDataTableToExcelHeader(DataTable dtList, string FilePath)
        {
            System.Data.DataTable dt = dtList;

            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            int iRow = 0;
            int iCol = 0;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    // workbook = new XSSFWorkbook();//office 2007

                    // 2007版本  
                    if (FilePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook();
                    // 2003版本  
                    else if (FilePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook();

                    sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet0的表 
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数//设置列头  

                    row = sheet.CreateRow(0);//excel第一行设为列头  

                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                        cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
                        cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                        cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                        cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                    }
                    //设置每行每列的单元格,  
                    //设置每行每列的单元格,  
                    for (int i = 0; i < rowCount; i++)
                    {
                        iRow = i;
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            //转换为数字格式
                            iCol = j;

                            cell = row.CreateCell(j);//excel第二行开始写入数据  
                            if (dt.Columns[j].DataType.ToString() != "System.Byte[]")
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            else
                                cell.SetCellValue("");
                        }
                    }
                    using (fs = File.OpenWrite(FilePath))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据  
                        fs.Close();
                        workbook.Close();
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {

                if (fs != null)
                {
                    fs.Close();
                    workbook.Close();
                }
                XtraMessageBox.Show("第" + iRow.ToString() + "行" + "第" + iCol.ToString() + "列" + ex.Message);
                return false;

            }
        }
        /// <summary>
        /// 导出gridview数据 即GridView类型
        /// </summary>
        /// <param name="view"></param>
        public static void GridViewToExcel(GridView view)
        {
            try
            {
               
                string path = @"D:\MESTemp";
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Title = "导出Excel";
                dlg.InitialDirectory = path;//导出默认路径
                dlg.Filter = "Excel文件(*.xlsx)|*.xlsx";//导出默认格式
                dlg.FileName = "MES_TEMP_" + DateTime.Now.ToString("yyyyMMdd_HHmmss");//导出默认名称
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    //view.ExportToPdf(dlg.FileName);
                    //view.ExportToXls(dlg.FileName);
                    Lamex.Common.ExecelHelper.ExportToExcelHeader(view, dlg.FileName);
                    if (DevExpress.XtraEditors.XtraMessageBox.Show("导出成功，是否打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                        System.Diagnostics.Process.Start(dlg.FileName);//打开指定路径下的文件
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// 传参数gridControl 即GridControl类型
        /// </summary>
        /// <param name="view"></param>
        /// <param name="Tilte"></param>
        public static void GridViewToPrint(GridControl gridControl,string Tilte)
        {
            try
            {
                PrintingSystem ps = new DevExpress.XtraPrinting.PrintingSystem();
                DevExpress.XtraPrinting.PrintableComponentLink link = new DevExpress.XtraPrinting.PrintableComponentLink(ps);
                ps.Links.Add(link);
                link.Component = gridControl;//这里可以是可打印的部件
                link.Landscape = true;
                link.Margins = new Margins(10, 10, 50, 50);
                string _PrintHeader = Tilte;
                PageHeaderFooter phf = link.PageHeaderFooter as PageHeaderFooter;
                phf.Header.Content.Clear();
                phf.Header.Content.AddRange(new string[] { "", _PrintHeader, "" });
                phf.Header.Font = new System.Drawing.Font("宋体", 14, System.Drawing.FontStyle.Bold); phf.Header.LineAlignment = BrickAlignment.Center;
                link.CreateDocument(); //建立文档
                ps.PrintDlg();//进行预览
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #region CSV生成DataTable
        //Creo特征数据保存表
        public static DataTable GetTable_FromCreo()
        {
            DataTable dtNew = new DataTable();//动态生成输出表
            dtNew.Columns.Add("FILE_NAME");
            dtNew.Columns.Add("DWG_NO");
            dtNew.Columns.Add("MODULE_CODE");
            dtNew.Columns.Add("PART_NO");
            dtNew.Columns.Add("PART_LENGTH");
            dtNew.Columns.Add("WORK_SURFACE");
            dtNew.Columns.Add("FACE_WIDTH");
            dtNew.Columns.Add("FACE_HEIGHT");
            dtNew.Columns.Add("UDF_NAME");
            dtNew.Columns.Add("FEATURE_TYPE");
            dtNew.Columns.Add("DEEP_CODE");
            dtNew.Columns.Add("COORD_X");
            dtNew.Columns.Add("COORD_Y");
            dtNew.Columns.Add("COORD_Z");
            dtNew.Columns.Add("FEATURE_DIA");
            dtNew.Columns.Add("SLOT_LENGTH");
            dtNew.Columns.Add("SLOT_WIDTH");
            dtNew.Columns.Add("FILLIT_RADIUS");
            dtNew.Columns.Add("FEATURE_DEPTH");
            dtNew.Columns.Add("CREO_OUTPUT_TIME");
            dtNew.Columns.Add("UPDATE_TIME");
            dtNew.Columns.Add("UPDATE_USER");
            return dtNew;
        }
        /// <summary>
        /// CSV转换成DataTable（文件流方式）
        /// </summary>
        /// <param name="csvPath">csv文件路径</param>
        /// <returns></returns>
        public static DataTable CSVToDataTableByStreamReader(string csvPath)
        {
            DataTable csvdt = new DataTable("csv");

            int intColCount = 0;
            bool blnFlag = true;
            DataColumn column;
            DataRow row;
            string strline = null;
            string[] aryline;
            Encoding encoding = GetType(csvPath);//null;

            using (StreamReader reader = new StreamReader(csvPath, encoding))
            {
                while (!string.IsNullOrEmpty((strline = reader.ReadLine())))
                {
                    aryline = strline.Split(new char[] { ',' });

                    if (blnFlag)
                    {
                        blnFlag = false;
                        intColCount = aryline.Length;
                        for (int i = 0; i < aryline.Length; i++)
                        {
                            column = new DataColumn(aryline[i].Trim('"'));
                            csvdt.Columns.Add(column);
                        }
                        continue;
                    }

                    row = csvdt.NewRow();
                    for (int i = 0; i < intColCount; i++)
                    {
                        row[i] = aryline[i].ToString().Trim('"');
                    }
                    csvdt.Rows.Add(row);
                }
            }

            return csvdt;
        }
        public static Encoding GetType(string FILE_NAME)
        {
            FileStream fs = new FileStream(FILE_NAME, FileMode.Open, FileAccess.Read);
            Encoding r = GetType(fs);
            fs.Close();
            return r;
        }
        /// 通过给定的文件流，判断文件的编码类型  
        /// <param name="fs">文件流</param>  
        /// <returns>文件的编码类型</returns>  
        public static System.Text.Encoding GetType(System.IO.FileStream fs)
        {
            byte[] Unicode = new byte[] { 0xFF, 0xFE, 0x41 };
            byte[] UnicodeBIG = new byte[] { 0xFE, 0xFF, 0x00 };
            byte[] UTF8 = new byte[] { 0xEF, 0xBB, 0xBF }; //带BOM  
            System.Text.Encoding reVal = System.Text.Encoding.Default;

            System.IO.BinaryReader r = new System.IO.BinaryReader(fs, System.Text.Encoding.Default);
            int i;
            int.TryParse(fs.Length.ToString(), out i);
            byte[] ss = r.ReadBytes(i);
            if (IsUTF8Bytes(ss) || (ss[0] == 0xEF && ss[1] == 0xBB && ss[2] == 0xBF))
            {
                reVal = System.Text.Encoding.UTF8;
            }
            else if (ss[0] == 0xFE && ss[1] == 0xFF && ss[2] == 0x00)
            {
                reVal = System.Text.Encoding.BigEndianUnicode;
            }
            else if (ss[0] == 0xFF && ss[1] == 0xFE && ss[2] == 0x41)
            {
                reVal = System.Text.Encoding.Unicode;
            }
            r.Close();
            return reVal;
        }
        /// 判断是否是不带 BOM 的 UTF8 格式  
        /// <param name="data"></param>  
        /// <returns></returns>  
        private static bool IsUTF8Bytes(byte[] data)
        {
            int charByteCounter = 1;　 //计算当前正分析的字符应还有的字节数  
            byte curByte; //当前分析的字节.  
            for (int i = 0; i < data.Length; i++)
            {
                curByte = data[i];
                if (charByteCounter == 1)
                {
                    if (curByte >= 0x80)
                    {
                        //判断当前  
                        while (((curByte <<= 1) & 0x80) != 0)
                        {
                            charByteCounter++;
                        }
                        //标记位首位若为非0 则至少以2个1开始 如:110XXXXX...........1111110X　  
                        if (charByteCounter == 1 || charByteCounter > 6)
                        {
                            return false;
                        }
                    }
                }
                else
                {
                    //若是UTF-8 此时第一位必须为1  
                    if ((curByte & 0xC0) != 0x80)
                    {
                        return false;
                    }
                    charByteCounter--;
                }
            }
            if (charByteCounter > 1)
            {
                throw new Exception("非预期的byte格式");
            }
            return true;
        }
        #endregion
        /// <summary>
        /// 将Csv读入DataTable
        /// </summary>
        /// <param name="filePath">csv文件路径</param>
        /// <param name="n">表示第n行是字段title,第n+1行是记录开始</param>
        /// <param name="k">可选参数表示最后K行不算记录默认0</param>
        public static DataTable csv2dt(string filePath, int n, DataTable dt) //这个dt 是个空白的没有任何行列的DataTable
        {
            StreamReader reader = null;
            try
            {
                reader= new StreamReader(filePath, System.Text.Encoding.Default, false);
                String csvSplitBy = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)";
                
                int i = 0, m = 0;
                reader.Peek();
                while (reader.Peek() > 0)
                {
                    m = m + 1;
                    string str = reader.ReadLine();
                    if (m >= n + 1)
                    {
                        if (m == n + 1) //如果是字段行，则自动加入字段。
                        {
                            MatchCollection mcs = Regex.Matches(str, csvSplitBy);
                            foreach (Match mc in mcs)
                            {
                                string name = mc.Value.Replace("\"", "");
                                dt.Columns.Add(name); //增加列标题
                            }

                        }
                        else
                        {
                            MatchCollection mcs = Regex.Matches(str, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
                            i = 0;
                            System.Data.DataRow dr = dt.NewRow();
                            foreach (Match mc in mcs)
                            {
                                string name = mc.Value.Replace("\"", "");
                                dr[i] = name;
                                i++;
                            }
                            dt.Rows.Add(dr);  //DataTable 增加一行     
                        }

                    }
                }
                reader.Dispose();
                
                reader.Close();
                return dt;
            }
            catch (Exception ex)
            {
                
                
                XtraMessageBox.Show(ex.Message);
                reader.Close();
                return null;
            }
        }
    }
}
